﻿using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;

namespace Beryl.PrintControllers
{
    public class PrintDataQuery
    {
        //流传单查询
        public static List<string> TableQuery(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string orderNum = k["orderNum"].ToString();
                string saleNum = k["saleNum"].ToString();
                string cSpecifucation = k["cSpecifucation"].ToString();
                string STime = k["STime"].ToString();
                string ETime = k["ETime"].ToString();
                string cProductDepNo = k["cProductDepNo"].ToString();
                string state = k["state"].ToString();

                switch(state)
                {
                    case "全部":
                        state = "";
                        break;
                    case "未打印":
                        state = "0";
                        break;
                    case "已打印":
                        state = "1";
                        break;
                }

                string sql = string.Format("select * from View_LCDPrint where cProductDepNo = '{0}' ", cProductDepNo);
                if(orderNum != "")
                {
                    sql += string.Format(@"and (cMOLotNo like '%{0}%') ",orderNum);
                }
                if(saleNum != "")
                {
                    sql += string.Format(@"and (cParentSONo like '%{0}%') ", saleNum);
                }
                if(cSpecifucation != "")
                {
                    sql += string.Format(@"and (cSpecifucation like '%{0}%') ", cSpecifucation);
                }
                if(state != "")
                {
                    sql += string.Format(@"and(isPrint = '{0}') ",state);
                }

                if (STime != "" && ETime != "")
                {
                    sql += string.Format(@"and (cMakeTime  between '{0}' and '{1}') ",STime,ETime);
                }

                if (STime != "" && ETime == "")
                {
                    sql += string.Format(@"and cMakeTime >= '{0}' ",STime);
                }

                if (STime == "" && ETime != "")
                {
                    sql += string.Format(@"and cMakeTime <= '{0}'",ETime);
                } 
                bool isAlpha = Regex.IsMatch(orderNum, @"^[a-zA-Z]+$");
                if (orderNum != "" && isAlpha)
                {
                    sql += " order by cMakeTime desc, cMOLotNo ";
                }else
                {
                    sql += " order by cMakeTime desc, substring(cMOLotNo, 1, 9), cast(substring(cMOLotNo, 13, 5) as int) ";
                }

                string ret = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if(ret.Equals("[]"))
                {
                    throw new Exception("查询数据为空");
                }
                list.Add(ret);
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //重工流传单查询
        public static List<string> ReWorkQuery(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string cProductDepNo = k["cProductDepNo"].ToString();
                string prodOrderNo = k["prodOrderNo"].ToString();
                string cSourceNo = k["cSourceNo"].ToString();
                string cSpecifucation = k["cSpecifucation"].ToString();
                string date1 = k["date1"].ToString();
                string date2 = k["date2"].ToString();
                string state = k["state"].ToString();

                switch (state)
                {
                    case "全部":
                        state = "";
                        break;
                    case "未打印":
                        state = "0";
                        break;
                    case "已打印":
                        state = "1";
                        break;
                }

                string sql = string.Format(@"select * from View_ReworkLCDPrint where Id > 0 and cProductDepNo = '{0}' ",cProductDepNo);

                if (prodOrderNo != "")
                {
                    sql +=  string.Format(@"and(cMOLotNo like '%{0}%') ",prodOrderNo);
                }

                if (cSourceNo != "")
                {
                    sql += string.Format(@"and(cSourceNo like '%{0}%') ",cSourceNo);
                }


                if (cSpecifucation != "")
                {
                    sql += string.Format(@"and(cSpecifucation like '%{0}%') ",cSpecifucation);
                }

                if (state != "")
                {
                    sql += string.Format(@"and(isPrint = '{0}') ",state);
                }

                if (date1 != "" && date2 != "")
                {
                    sql += string.Format(@"and(cMakeTime  between '{0}' and '{1}') ",date1,date2);
                }

                if (date1 != "" && date2 == "")
                {
                    sql += string.Format(@"and cMakeTime >= '{0}' ",date1);
                }

                if (date1 == "" && date2 != "")
                {
                    sql += string.Format(@"and cMakeTime <= '{0}' ",date2);
                }

                bool isAlpha = Regex.IsMatch(prodOrderNo, @"^[a-zA-Z]+$");
                if (prodOrderNo != "" && isAlpha)
                {
                    sql += " order by cMakeTime desc, cMOLotNo ";
                }
                else
                {
                    sql += " order by cMakeTime desc, substring(cMOLotNo, 1, 9), cast(substring(cMOLotNo, 13, 5) as int) ";
                }
                string ret = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if (ret.Equals("[]"))
                {
                    throw new Exception("查询数据为空");
                }
                list.Add(ret);
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //裁切流传单查询信息
        public static List<string> CuttingQuery(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            try
            {
                var k = jArray[0];
                string cMOLotNo = k["cMOLotNo"].ToString();
                string cSpecifucation = k["cSpecifucation"].ToString();
                string date1 = k["date1"].ToString();
                string date2 = k["date2"].ToString();
                string selct = k["selct"].ToString();
                string state = k["state"].ToString();
                string cProductDepNo = k["cProductDepNo"].ToString();
                switch (state)
                {
                    case "全部":
                        state = "";
                        break;
                    case "未打印":
                        state = "0";
                        break;
                    case "已打印":
                        state = "1";
                        break;
                }

                string sql = string.Format(@"select * from View_CQLCDPrint where cProductDepNo = '{0}' ",cProductDepNo);

                if (cMOLotNo != "")
                {
                    sql += string.Format(@"and(cMOLotNo like '%{0}%') ", cMOLotNo);
                }

                if (cSpecifucation != "")
                {
                    sql += string.Format(@"and(cSpecifucation like '%{0}%') ", cSpecifucation);
                }

                if (state != "")
                {
                    sql += string.Format(@"and(isPrint = '{0}') ", state);
                }

                if (date1 != "" && date2 != "")
                {
                    sql += string.Format(@"and(cMakeTime  between '{0}' and '{1}') ", date1, date2);
                }

                if (date1 != "" && date2 == "")
                {
                    sql += string.Format(@"and cMakeTime >= '{0}' ", date1);
                }

                if (date1 == "" && date2 != "")
                {
                    sql += string.Format(@"and cMakeTime <= '{0}' ", date2);
                }

                if (selct != "")
                {
                    sql += string.Format(@"and cMatName LIKE '%{0}' ",selct);
                }
                sql += "order by dMakePlanDate desc, cMOLotNo ";

                string ret = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if (ret.Equals("[]"))
                {
                    throw new Exception("查询数据为空");
                }
                list.Add(ret);
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //查询导入数据中的流传单号
        public static List<string> QueryTable(string para)
        {
            List<string> list = new List<string>();
            try
            {
                var sql = string.Format(@"select * from View_LCDPrint where cMOLotNo in ({0})",para);
                string ret = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if (ret.Equals("[]"))
                {
                    throw new Exception("查询数据为空");
                }
                list.Add(ret);
            }
            catch(Exception ex)
            {
                list.Add(ex.Message);
            }
            return list;
        }
    }
}